using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.AceEngineering.SPM.SPMCustomDataEntity;

namespace Microsoft.AceEngineering.SPM.SPMDataLayer
{
    public class CategoryDL
    {
        public static void PublishStagingAssessment()
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;
            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "APM_spPublishStagingAssessment");
        }

        public static void CreateStagingAssessment()
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;
            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "APM_spCreateStagingAssessment");
        }

        public static List<CategoryCDE> GetCategory(string dir)
        {
            SqlDataReader sqlreader = null;
            List<CategoryCDE> categoryList = new List<CategoryCDE>();
            CategoryCDE category = null;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                SqlParameter[] myParams = new SqlParameter[1];
                myParams[0] = new SqlParameter("@dir", SqlDbType.VarChar, 10);
                myParams[0].Value = GiveMeDir(dir);

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "APM_spgetStagingCategoryWSort", myParams);

                while (sqlreader.Read())
                {
                    category = new CategoryCDE();
                    category.QuestionCategoryID = sqlreader.GetValue(0).ToString();
                    category.CategoryName = sqlreader.GetValue(1).ToString();

                    categoryList.Add(category);
                    category = null;
                }
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return categoryList;
        }

        public static void UpdateCategory(string questionID, string categoryName, string createdBy)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[3];
            myParams[0] = new SqlParameter("@QuestionCategoryID", SqlDbType.VarChar, 200);
            myParams[0].Value = questionID;

            myParams[1] = new SqlParameter("@CategoryName", SqlDbType.VarChar, 50);
            myParams[1].Value = categoryName;

            myParams[2] = new SqlParameter("@CreatedBy", SqlDbType.VarChar, 50);
            myParams[2].Value = createdBy;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spUpdateCategories", myParams);
        }

        public static void DeleteCategory(string questionID)
        {
            string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

            SqlParameter[] myParams = new SqlParameter[1];
            myParams[0] = new SqlParameter("@QuestionCategoryID", SqlDbType.VarChar, 200);
            myParams[0].Value = questionID;

            SqlHelper.ExecuteNonQuery(conString, CommandType.StoredProcedure, "SPM_spDeleteCategories", myParams);
        }

        public static int AssessmentPublished()
        {
            SqlDataReader sqlreader = null;
            int total = 0;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spAssessmentPublished");

                sqlreader.Read();
                total = Int32.Parse(sqlreader[0].ToString());
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return total;
        }

        public static int AssessmentPublishable()
        {
            SqlDataReader sqlreader = null;
            int total = 0;

            try
            {
                string conString = ConfigurationManager.ConnectionStrings["SPMSQL"].ConnectionString;

                sqlreader = SqlHelper.ExecuteReader(conString, CommandType.StoredProcedure, "SPM_spAssessmentPublishable");

                sqlreader.Read();
                total = Int32.Parse(sqlreader[0].ToString());
            }
            finally
            {
                if (sqlreader != null)
                {
                    if (!sqlreader.IsClosed)
                        sqlreader.Close();
                }
            }
            return total;
        }

        private static string GiveMeDir(string sortExp)
        {
            if (sortExp.Contains("DESC"))
                return "DESC";
            else
                return "ASC";
        }
    }
}
